package com.x.erp.dao;

import com.x.erp.model.SelectTemplate;
import com.x.framework.dao.BaseDao;
import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * 查询订阅信息DAO
 */
@Repository
public class SelectTemplateDao extends BaseDao {

	private final static String updateSelectTemplateSetSql = "update ERP_SELECT_TEMPLATE est SET ";
	private final static String updateSelectTemplateWhereSql = "est.TEMPLATE_UPDATE_TIME = ? WHERE est.TEMPLATE_ID = ? ";

	/**
	 * 查询订阅信息
	 * @param selectTemplate
	 * @return
	 * @throws Exception
	 */
	public int updateSelectTemplate(SelectTemplate selectTemplate) throws Exception {
		StringBuilder updateSql = new StringBuilder(updateSelectTemplateSetSql);
		StringBuilder whereSql = new StringBuilder(updateSelectTemplateWhereSql);
		List<Object> params = new ArrayList<>();
		updateCondition(updateSql, params, selectTemplate);
		updateSql.append(whereSql);
		params.add(new Date());
		params.add(selectTemplate.getTemplateId());
		return this.execute(updateSql.toString(), params);
	}

	private void updateCondition(StringBuilder updateSql, List<Object> params, SelectTemplate selectTemplate) {
		if(selectTemplate != null){
			// 城市Id
			updateSql.append("est.TEMPLATE_CITY_ID = ?, ");
			params.add(selectTemplate.getTemplateCityId());
			// 城市名称
			updateSql.append("est.TEMPLATE_CITY_NAME = ?, ");
			params.add(selectTemplate.getTemplateCityName());
			// 品牌Id
			updateSql.append("est.TEMPLATE_BRAND_ID = ?, ");
			params.add(selectTemplate.getTemplateBrandId());
			// 品牌名称
			updateSql.append("est.TEMPLATE_BRAND_NAME = ?, ");
			params.add(selectTemplate.getTemplateBrandName());
			// 车系Id
			updateSql.append("est.TEMPLATE_SERIES_ID = ?, ");
			params.add(selectTemplate.getTemplateSeriesId());
			// 车系名称
			updateSql.append("est.TEMPLATE_SERIES_NAME = ?, ");
			params.add(selectTemplate.getTemplateSeriesName());
			// 起始价格
			updateSql.append("est.TEMPLATE_PRICE_FROM = ?, ");
			params.add(selectTemplate.getTemplatePriceFrom());
			// 结束价格
			updateSql.append("est.TEMPLATE_PRICE_TO = ?, ");
			params.add(selectTemplate.getTemplatePriceTo());
			// 起始车龄
			updateSql.append("est.TEMPLATE_CAR_AGE_FROM = ?, ");
			params.add(selectTemplate.getTemplateCarAgeFrom());
			// 结束车龄
			updateSql.append("est.TEMPLATE_CAR_AGE_TO = ?, ");
			params.add(selectTemplate.getTemplateCarAgeTo());
			// 起始里程
			updateSql.append("est.TEMPLATE_MILEAGE_FROM = ?, ");
			params.add(selectTemplate.getTemplateMileageFrom());
			// 结束里程
			updateSql.append("est.TEMPLATE_MILEAGE_TO = ?, ");
			params.add(selectTemplate.getTemplateMileageTo());
			// 变速箱
			updateSql.append("est.TEMPLATE_GEARBOX = ?, ");
			params.add(selectTemplate.getTemplateGearbox());
			// 外观颜色
			updateSql.append("est.TEMPLATE_OUT_COLOR = ?, ");
			params.add(selectTemplate.getTemplateOutColor());
			// 车身类型Id
			updateSql.append("est.TEMPLATE_CAR_TYPE_ID =? , ");
			params.add(selectTemplate.getTemplateCarTypeId());
			// 车身类型Id
			updateSql.append("est.TEMPLATE_CAR_TYPE_NAME =? , ");
			params.add(selectTemplate.getTemplateCarTypeName());
			// 环保等级
			updateSql.append("est.TEMPLATE_ENVIRON = ?, ");
			params.add(selectTemplate.getTemplateEnviron());
			// 配置Id
			updateSql.append("est.TEMPLATE_CONFIG_ID = ?, ");
			params.add(selectTemplate.getTemplateConfigId());
			// 配置名称
			updateSql.append("est.TEMPLATE_CONFIG_NAME = ?, ");
			params.add(selectTemplate.getTemplateConfigName());
		}
	}

	private final static String selectSelectTemplateSql = "SELECT " +
			"est.TEMPLATE_ID, " +
			"est.TEMPLATE_CITY_ID, " +
			"est.TEMPLATE_CITY_NAME, " +
			"est.TEMPLATE_BRAND_ID, " +
			"est.TEMPLATE_BRAND_NAME, " +
			"est.TEMPLATE_SERIES_ID, " +
			"est.TEMPLATE_SERIES_NAME, " +
			"est.TEMPLATE_PRICE_FROM, " +
			"est.TEMPLATE_PRICE_TO, " +
			"est.TEMPLATE_CAR_AGE_FROM, " +
			"est.TEMPLATE_CAR_AGE_TO, " +
			"est.TEMPLATE_MILEAGE_FROM, " +
			"est.TEMPLATE_MILEAGE_TO, " +
			"est.TEMPLATE_GEARBOX, " +
			"est.TEMPLATE_OUT_COLOR, " +
			"est.TEMPLATE_CAR_TYPE_ID, " +
			"est.TEMPLATE_CAR_TYPE_NAME, " +
			"est.TEMPLATE_ENVIRON, " +
			"est.TEMPLATE_CONFIG_ID, " +
			"est.TEMPLATE_CONFIG_NAME, " +
			"est.TEMPLATE_CREATE_TIME, " +
			"est.TEMPLATE_UPDATE_TIME " +
			"FROM erp_select_template est " +
			"WHERE 1=1 ";

	/**
	 * 查询订阅信息
	 * @param selectTemplate
	 * @return
	 * @throws Exception
	 */
	public SelectTemplate selectSelectTemplate(SelectTemplate selectTemplate) throws Exception {
		StringBuilder sql = new StringBuilder(selectSelectTemplateSql);
		List<Object> params = new ArrayList<>();
		searchCondition(sql, params, selectTemplate);
		sql.append ("LIMIT 1 ");
		List<SelectTemplate> results = this.queryForList(sql.toString(), SelectTemplate.class, params);
		if (results != null && results.size() > 0) {
			return results.get(0);
		}
		return null;
	}

	private final static String selectSelectTemplateListSql = "SELECT " +
			"est.TEMPLATE_ID, " +
			"est.TEMPLATE_CITY_ID, " +
			"est.TEMPLATE_CITY_NAME, " +
			"est.TEMPLATE_BRAND_ID, " +
			"est.TEMPLATE_BRAND_NAME, " +
			"est.TEMPLATE_SERIES_ID, " +
			"est.TEMPLATE_SERIES_NAME, " +
			"est.TEMPLATE_PRICE_FROM, " +
			"est.TEMPLATE_PRICE_TO, " +
			"est.TEMPLATE_CAR_AGE_FROM, " +
			"est.TEMPLATE_CAR_AGE_TO, " +
			"est.TEMPLATE_MILEAGE_FROM, " +
			"est.TEMPLATE_MILEAGE_TO, " +
			"est.TEMPLATE_GEARBOX, " +
			"est.TEMPLATE_OUT_COLOR, " +
			"est.TEMPLATE_CAR_TYPE_ID, " +
			"est.TEMPLATE_CAR_TYPE_NAME, " +
			"est.TEMPLATE_ENVIRON, " +
			"est.TEMPLATE_CONFIG_ID, " +
			"est.TEMPLATE_CONFIG_NAME, " +
			"est.TEMPLATE_CREATE_TIME, " +
			"est.TEMPLATE_UPDATE_TIME " +
			"FROM erp_select_template est " +
			"WHERE 1=1 ";

	/**
	 * 查询订阅列表信息
	 * @param selectTemplate
	 * @return
	 * @throws Exception
	 */
	public List<SelectTemplate> selectSelectTemplateList(SelectTemplate selectTemplate) throws Exception {
		StringBuilder sql = new StringBuilder(selectSelectTemplateListSql);
		List<Object> params = new ArrayList<>();
		searchCondition(sql, params, selectTemplate);
		List<SelectTemplate> results = this.queryForList(sql.toString(), SelectTemplate.class, params);
		return results;
	}

	// 拼接查询条件
	private void searchCondition(StringBuilder sql, List<Object> params, SelectTemplate selectTemplate) {
		if(selectTemplate != null){
			if(StringUtils.isNotEmpty(selectTemplate.getTemplateId())){
				sql.append("AND est.TEMPLATE_ID = ? ");
				params.add(selectTemplate.getTemplateId());
			}
		}
	}
}
